﻿using Models.Common;
using Models.MVC;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Web.Filters;

namespace Web.Controllers.MVC
{
    [MvcAuthentication]
    public class Bill8Controller : Controller
    {
        // GET: Bill1
        public ActionResult DetailsList(int ID)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select BillIndex8.*,isnull(Goods.Code,'') as GoodsCode,isnull(Goods.Name,'') as GoodsName,isnull(Goods.Model,'') as GoodsModel
                ,isnull(BillIndex4.GoodsID,0) as SourceGoodsID
				,isnull(SGoods.Code,'') as SourceGoodsCode,isnull(SGoods.Name,'') as SourceGoodsName,isnull(SGoods.Model,'') as SourceGoodsModel
FROM BillIndex8 left join Goods on BillIndex8.GoodsID=Goods.ID
        left join BillIndex4 on BillIndex8.BillIndex4ID=BillIndex4.ID
        left join Goods as SGoods on BillIndex4.GoodsID=SGoods.ID
WHERE BillIndex8.ID=" + ID).Tables[0];
            string sssss = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
            Models.MVC.Bill8IndexModel m = new Models.MVC.Bill8IndexModel();
            if (dt.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.Bill8IndexModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];
                m.CreateDate = Convert.ToDateTime(m.CreateDate).ToString("yyyy-MM-dd");
            }
            ViewBag.model = m;

            return View();
        }
        public string DetailsListAsync(int IndexID)
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@IndexID",SqlDbType.Int),
            };
            parameters[0].Value = IndexID;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBill8BillDetailsList", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }

        // GET: Bill1
        public ActionResult IndexList()
        {
            return View();
        }

        [HttpPost]
        public string IndexListAsync(int PageIndex = 1, int PageSize = 1000000000, string Search = "", int State = 0)
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@PageIndex",SqlDbType.Int),
                                            new SqlParameter("@PageSize",SqlDbType.Int),
                                            new SqlParameter("@Search",SqlDbType.NVarChar,40000),
                                            new SqlParameter("@State",SqlDbType.Int),
            };
            parameters[0].Value = PageIndex;
            parameters[1].Value = PageSize;
            parameters[2].Value = Search;
            parameters[3].Value = State;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBill8IndexList", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        public ActionResult IndexEdit(int ID = 0)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select BillIndex8.*,isnull(Goods.Code,'') as GoodsCode,isnull(Goods.Name,'') as GoodsName,isnull(Goods.Model,'') as GoodsModel
                ,isnull(BillIndex4.GoodsID,0) as SourceGoodsID
				,isnull(SGoods.Code,'') as SourceGoodsCode,isnull(SGoods.Name,'') as SourceGoodsName,isnull(SGoods.Model,'') as SourceGoodsModel
FROM BillIndex8 left join Goods on BillIndex8.GoodsID=Goods.ID
        left join BillIndex4 on BillIndex8.BillIndex4ID=BillIndex4.ID
        left join Goods as SGoods on BillIndex4.GoodsID=SGoods.ID
WHERE BillIndex8.ID=" + ID).Tables[0];
            string sssss = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
            Models.MVC.Bill8IndexModel m = new Models.MVC.Bill8IndexModel();
            if (dt.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.Bill8IndexModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];
                m.CreateDate = Convert.ToDateTime(m.CreateDate).ToString("yyyy-MM-dd");
            }
            ViewBag.model = m;

            DataTable udt = FreeADO.DBUtility.DbHelperSQL.Query("select * from Users where DepartmentID=3 and IsDeleted=0").Tables[0];
            string s = "";
            foreach (DataRow item in udt.Rows)
            {
                s = s + @"<option value=""" + item["ID"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
            }
            ViewBag.Users = s;
            DataTable ddt = FreeADO.DBUtility.DbHelperSQL.Query("select * from DengJi").Tables[0];
            string ds = "";
            foreach (DataRow item in ddt.Rows)
            {
                ds = ds + @"<option value=""" + item["Name"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
            }
            ViewBag.DengJi = ds;

            if (ID == 0)
            {
                DataTable mdt = FreeADO.DBUtility.DbHelperSQL.Query("select * from BillTempletIndex where BillNameID=8 and IsDeleted=0").Tables[0];
                string ms = "";
                foreach (DataRow item in mdt.Rows)
                {
                    ms = ms + @"<option value=""" + item["ID"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
                }
                ViewBag.MoBan = ms;
            }
            else
            {
                ViewBag.MoBan = "";
            }


            return View();
        }
        [HttpPost]
        public ActionResult IndexEditAsync(Models.MVC.Bill8IndexModel m)
        {
            Result<string> result = Web.Common.Bill8.IndexEdit(m);
            return Json(result);

        }
        [HttpPost]
        public ActionResult IndexDelAsync(string IDs)
        {
            Result<string> result = new Result<string>();
            try
            {

                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"UPDATE BillIndex8 set IsDeleted=1 where ID in (" + IDs + @")");
                    result.Type = AjaxResultType.Success;
                    result.Content = "成功";
                    result.Data = "成功";

            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = e.Message;
            }
            return Json(result);
        }
        [HttpPost]
        public ActionResult ModifyIndexStateAsync(int ID, int State)
        {
            Result<decimal> result = new Result<decimal>() { Type = AjaxResultType.Success, Content = "成功", Data = 0, TotalRows = 0 };
            try
            {
                SqlParameter[] paras ={
                    new SqlParameter("@BillNameID",SqlDbType.Int),
                    new SqlParameter("@IndexID",SqlDbType.Int),
                    new SqlParameter("@Type",SqlDbType.Int),
                    new SqlParameter("@Str",SqlDbType.NVarChar,4000),
                    new SqlParameter("@ReturnValue", SqlDbType.Int)
                                       };
                paras[0].Value = 8;
                paras[1].Value = ID;
                paras[2].Value = State;
                paras[3].Value = "";
                paras[3].Direction = ParameterDirection.InputOutput;
                paras[4].Direction = ParameterDirection.ReturnValue;

                DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_ModifyIndexState", paras, "aaa", 3000);
                if (Convert.ToInt32(paras[4].Value) == 1)
                {
                    result.Type = AjaxResultType.Success;
                    result.Content = "成功";
                    result.Data = 0;
                }
                else
                {
                    result.Type = AjaxResultType.Error;
                    result.Content = paras[3].Value.ToString();
                    result.Data = 0;
                }
            }
            catch (Exception e)
            {
                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = 0;
            }
            return Json(result);
        }

        public ActionResult BillProceduresEdit(int IndexID = 0,int ID=0)
        {
            ViewBag.IndexID = IndexID;
            ViewBag.ID = ID;
            ViewBag.ProcedureID = 0;
            ViewBag.ShowIndex = 0;
            ViewBag.Remark = "";
            ViewBag.BiaoZhunQty = 1;
            ViewBag.GongYiID = 0;
            if (Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select count(*) from BillProcedures8 where IsDeleted=0 and BillIndexID=" + IndexID)) > 0)
            {
                ViewBag.ShowIndex = Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select max(ShowIndex) from BillProcedures8 where IsDeleted=0 and BillIndexID=" + IndexID)) + 1;
            }

            if (ID > 0)
            {
                DataTable fdt = FreeADO.DBUtility.DbHelperSQL.Query("select * from BillProcedures8 where ID=" + ID).Tables[0];
                ViewBag.ProcedureID = fdt.Rows[0]["ProcedureID"].ToString();
                ViewBag.ShowIndex = fdt.Rows[0]["ShowIndex"].ToString();
                ViewBag.Remark = fdt.Rows[0]["Remark"].ToString();
                ViewBag.BiaoZhunQty = fdt.Rows[0]["BiaoZhunQty"].ToString();
                ViewBag.GongYiID = fdt.Rows[0]["GongYiID"].ToString();
            }
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query("select * from Procedures where IsDeleted=0 and State=1 and BillNameID=8").Tables[0];
            string s = "";
            foreach (DataRow item in dt.Rows)
            {
                s = s + @"<option value=""" + item["ID"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
            }
            ViewBag.Procedures = s;

            return View();
        }
        [HttpPost]
        public ActionResult BillProceduresEditAsync(int IndexID = 0, int ID = 0, int ProcedureID = 0, string Remark = "",int ShowIndex=0, decimal BiaoZhunQty = 0,int GongYiID=0)
        {
            Result<int> result = new Result<int>();
            if (BiaoZhunQty <= 0)
            {
                result.Type = AjaxResultType.Error;
                result.Content = "工时标准必须大于0！";
                result.Data = 0;
                return Json(result);
            }
            try
            {
                if (ID == 0)
                {
                    if (Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select count(*) from BillProcedures8 where BillIndexID=" + IndexID + " and ShowIndex=" + ShowIndex + " and IsDeleted=0")) > 0)
                    {
                        FreeADO.DBUtility.DbHelperSQL.ExecuteSql("UPDATE BillProcedures8 set ShowIndex=ShowIndex+1 where ShowIndex>=" + ShowIndex + " and BillIndexID=" + IndexID + " and IsDeleted=0");
                    }

                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql("insert into BillProcedures8(BillIndexID,ProcedureID,Remark,ShowIndex,BiaoZhunQty,GongYiID) values (" + IndexID + "," + ProcedureID + ",@Remark," + ShowIndex + "," + BiaoZhunQty.ToString() + ","+GongYiID+")", new SqlParameter[1] { new SqlParameter("@Remark", Remark) });
                }
                else
                {
                    if (Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select count(*) from BillProcedures8 where BillIndexID=" + IndexID + " and ShowIndex=" + ShowIndex + " and IsDeleted=0 and ID<>" + ID)) > 0)
                    {
                        FreeADO.DBUtility.DbHelperSQL.ExecuteSql("UPDATE BillProcedures8 set ShowIndex=ShowIndex+1 where ShowIndex>=" + ShowIndex + " and BillIndexID=" + IndexID + " and IsDeleted=0 and ID<>" + ID);
                    }
                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql("update BillProcedures8 set ProcedureID=" + ProcedureID + ",Remark=@Remark,ShowIndex=" + ShowIndex + @",BiaoZhunQty=" + BiaoZhunQty.ToString() + ", GongYiID=" + GongYiID + " where ID=" + ID, new SqlParameter[1] { new SqlParameter("@Remark", Remark) });
                }
                result.Type = AjaxResultType.Success;
                result.Content = "成功。";
                result.Data = 0;

            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = 0;
            }
            return Json(result);
        }

        public ActionResult BillDetailsEdit(int BillProcedureID = 0, int ID = 0)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query("select * from BillDetails8 where ID=" + ID).Tables[0];
            Bill8DetailsModel m = new Bill8DetailsModel();
            m.BillProcedureID = BillProcedureID;
            m.ShaiXuanModel = FreeADO.DBUtility.DbHelperSQL.GetSingle("select Remark from BillProcedures8 where ID=" + BillProcedureID).ToString();
            m.InQty = Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select dbo.Fn_Value_GetOutQty(8," + BillProcedureID + ")"));
            m.OutQty = m.InQty;
            m.OperatorUserID = Common.CookiesManagement.GetUserModel().UserID;
            if (dt.Rows.Count>0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.Bill8DetailsModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];
                m.CreateDate = Convert.ToDateTime(m.CreateDate).ToString("yyyy-MM-dd");
                m.BeginDate = Convert.ToDateTime(m.BeginDate) < new DateTime(2000, 01, 01) ? "" : Convert.ToDateTime(m.BeginDate).ToString("yyyy-MM-dd");
                m.EndDate = Convert.ToDateTime(m.EndDate) < new DateTime(2000, 01, 01) ? "" : Convert.ToDateTime(m.EndDate).ToString("yyyy-MM-dd");
            }
            ViewBag.model = m;
            DataTable udt = FreeADO.DBUtility.DbHelperSQL.Query("select * from Users where DepartmentID=3 and IsDeleted=0").Tables[0];
            string s = "";
            foreach (DataRow item in udt.Rows)
            {
                s = s + @"<option value=""" + item["ID"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
            }
            ViewBag.Users = s;

            return View();
        }
        [HttpPost]
        public ActionResult BillDetailsEditAsync(Bill8DetailsModel m)
        {
            Result<int> result = Web.Common.Bill8.BillDetailsEdit(m);
            return Json(result);
        }
        [HttpPost]
        public ActionResult BillProceduresDelAsync(string IDs)
        {
            Result<string> result = new Result<string>();
            try
            {

                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"Delete BillDetails8 where BillProcedureID in (" + IDs + @")");
                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"Delete BillProcedures8 where ID in (" + IDs + @")");
                result.Type = AjaxResultType.Success;
                result.Content = "成功";
                result.Data = "成功";
            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = e.Message;
            }
            return Json(result);
        }
        [HttpPost]
        public ActionResult BillDetailsDelAsync(string IDs)
        {
            Result<string> result = new Result<string>();
            try
            {

                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"Delete BillDetails8 where ID in (" + IDs + @")");
                result.Type = AjaxResultType.Success;
                result.Content = "成功";
                result.Data = "成功";
            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = e.Message;
            }
            return Json(result);
        }
        public ActionResult BillDetailsStuffList(int BillDetailsID)
        {
            ViewBag.ID = BillDetailsID;
            return View();
        }

        [HttpPost]
        public string BillDetailsStuffListAsync(int BillDetailsID)
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@BillDetailsID",SqlDbType.Int),
            };
            parameters[0].Value = BillDetailsID;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBill8DetailsStuffList", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        public ActionResult BillDetailsStuffEdit(int BillDetailsID,int ID = 0)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select a.Qty,a.GoodsID,b.TypeID,a.ID
FROM BillDetailsStuff8 as a inner join Goods b on a.GoodsID=b.ID
WHERE a.ID=" + ID).Tables[0];
            ViewBag.BillDetailsID = BillDetailsID;
            ViewBag.ID = ID;
            ViewBag.Qty = 0;
            ViewBag.GoodsID = 0;
            ViewBag.GoodsType = 1;
            Models.MVC.Bill1IndexModel m = new Models.MVC.Bill1IndexModel();
            if (dt.Rows.Count > 0)
            {
                ViewBag.Qty = dt.Rows[0]["Qty"].ToString();
                ViewBag.GoodsID = dt.Rows[0]["GoodsID"].ToString();
                ViewBag.GoodsType = dt.Rows[0]["TypeID"].ToString();
            }
            ViewBag.model = m;




            return View();
        }
        [HttpPost]
        public ActionResult BillDetailsStuffEditAsync(int BillDetailsID,int GoodsID,int Qty,int ID)
        {
            Result<string> result = new Result<string>();
            try
            {
                if (ID == 0)
                {
                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"
INSERT INTO [dbo].[BillDetailsStuff8]
           ([BillDetailsID]
           ,[GoodsID]
           ,[Qty])
     VALUES
           (
'" + BillDetailsID + @"',
'" + GoodsID + @"',
'" + Qty + @"'
)
");
                    result.Type = AjaxResultType.Success;
                    result.Content = "成功";
                    result.Data = "成功";
                }
                else
                {

                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"
UPDATE [dbo].[BillDetailsStuff8]
   SET [BillDetailsID] = '" + BillDetailsID + @"'
      ,[GoodsID] = '" + GoodsID + @"'
      ,[Qty] = '" + Qty + @"'
 WHERE ID=" + ID + @"
");
                    result.Type = AjaxResultType.Success;
                    result.Content = "成功";
                    result.Data = "成功";
                }
            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = e.Message;
            }
            return Json(result);
        }
        [HttpPost]
        public ActionResult BillDetailsStuffDelAsync(string IDs)
        {
            Result<string> result = new Result<string>();
            try
            {

                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"Delete BillDetailsStuff8 where ID in (" + IDs + @")");
                result.Type = AjaxResultType.Success;
                result.Content = "成功";
                result.Data = "成功";
            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = e.Message;
            }
            return Json(result);
        }
        public ActionResult JiaoFuList(int ID)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select BillIndex8.*,isnull(Goods.Code,'') as GoodsCode,isnull(Goods.Name,'') as GoodsName,isnull(Goods.Model,'') as GoodsModel
                ,isnull(BillIndex4.GoodsID,0) as SourceGoodsID
				,isnull(SGoods.Code,'') as SourceGoodsCode,isnull(SGoods.Name,'') as SourceGoodsName,isnull(SGoods.Model,'') as SourceGoodsModel
FROM BillIndex8 left join Goods on BillIndex8.GoodsID=Goods.ID
        left join BillIndex4 on BillIndex8.BillIndex4ID=BillIndex4.ID
        left join Goods as SGoods on BillIndex4.GoodsID=SGoods.ID
WHERE BillIndex8.ID=" + ID).Tables[0];
            string sssss = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
            Models.MVC.Bill8IndexModel m = new Models.MVC.Bill8IndexModel();
            if (dt.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.Bill8IndexModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];
                m.CreateDate = Convert.ToDateTime(m.CreateDate).ToString("yyyy-MM-dd");
            }
            ViewBag.model = m;

            return View();
        }
        [HttpPost]
        public string JiaoFuListAsync(int IndexID)
        {

            GridResult result = new GridResult();

            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select ID,CONVERT(nvarchar(50),CreateTime,120) as CreateTime,Qty,HQty ,Number,Remark
from GoodsStockRunnings where BillNameID=8 and Type=1 and BillIndexID=" + IndexID + @"
order by ID desc").Tables[0];
            result.count = 0;
            result.data = dt;
            result.totalRow = new Dictionary<string, string>();
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        public ActionResult JiaoFuEdit(int IndexID, int ID = 0)
        {
            ViewBag.IndexID = IndexID;
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"select * from GoodsStockRunnings WHERE ID=" + ID).Tables[0];
            string sssss = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
            Models.MVC.JiaoFuModel m = new Models.MVC.JiaoFuModel();
            if (dt.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.JiaoFuModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];
                m.CreateTime = Convert.ToDateTime(m.CreateTime).ToString("yyyy-MM-dd");
            }
            ViewBag.model = m;

            return View();
        }
        [HttpPost]
        public ActionResult JiaoFuEditAsync(int IndexID, decimal Qty,decimal HQty=0, int ID = 0, string Remark = "", string Number = "", int IsExec = 0, string CreateTime = "")
        {
            Result<decimal> result = new Result<decimal>() { Type = AjaxResultType.Success, Content = "成功", Data = 0, TotalRows = 0 };
            result = Web.Common.BillCommon.JiaoFuEditAsync(8, IndexID, Qty, ID, Remark, Number, IsExec, CreateTime,HQty);

            return Json(result);
        }

        [HttpPost]
        public ActionResult ShowIndexEditAsync(int IndexID, int BillProcedureID, int ShowIndex)
        {
            Result<decimal> result = new Result<decimal>() { Type = AjaxResultType.Success, Content = "成功", Data = 0, TotalRows = 0 };
            try
            {
                if (Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select count(*) from BillProcedures8 where BillIndexID=" + IndexID + " and ShowIndex=" + ShowIndex + " and IsDeleted=0")) > 0)
                {
                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql("UPDATE BillProcedures8 set ShowIndex=ShowIndex+1 where ShowIndex>=" + ShowIndex + " and BillIndexID=" + IndexID + " and IsDeleted=0");
                }

                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"UPDATE BillProcedures8 set ShowIndex=" + ShowIndex + " where ID=" + BillProcedureID);
            }
            catch (Exception e)
            {
                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = 0;
            }
            return Json(result);
        }


        public ActionResult IndexListReport1()
        {
            return View();
        }

        [HttpPost]
        public string IndexListReport1Async(int PageIndex = 1, int PageSize = 1000000000, string Search = "", string BeginDate = "", string EndDate = "", int State = -1)
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@BillNameID",SqlDbType.Int),
                                            new SqlParameter("@PageIndex",SqlDbType.Int),
                                            new SqlParameter("@PageSize",SqlDbType.Int),
                                            new SqlParameter("@Search",SqlDbType.NVarChar,40000),
                                            new SqlParameter("@BeginDate",SqlDbType.DateTime),
                                            new SqlParameter("@EndDate",SqlDbType.DateTime),
                                            new SqlParameter("@State",SqlDbType.Int),

                };
            parameters[0].Value = 8;
            parameters[1].Value = PageIndex;
            parameters[2].Value = PageSize;
            parameters[3].Value = Search;
            parameters[4].Value = BeginDate;
            parameters[5].Value = EndDate;
            parameters[6].Value = State;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBillIndexListReport", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        public ActionResult IndexListReport2()
        {
            return View();
        }

        [HttpPost]
        public string IndexListReport2Async(int PageIndex = 1, int PageSize = 1000000000, string Search = "", string BeginDate = "", string EndDate = "", int State = -1)
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@BillNameID",SqlDbType.Int),
                                            new SqlParameter("@PageIndex",SqlDbType.Int),
                                            new SqlParameter("@PageSize",SqlDbType.Int),
                                            new SqlParameter("@Search",SqlDbType.NVarChar,40000),
                                            new SqlParameter("@BeginDate",SqlDbType.DateTime),
                                            new SqlParameter("@EndDate",SqlDbType.DateTime),
                                            new SqlParameter("@State",SqlDbType.Int),

                };
            parameters[0].Value = 8;
            parameters[1].Value = PageIndex;
            parameters[2].Value = PageSize;
            parameters[3].Value = Search;
            parameters[4].Value = BeginDate;
            parameters[5].Value = EndDate;
            parameters[6].Value = State;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBillFailQtyReport", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        public ActionResult IndexListReport3()
        {
            return View();
        }

        [HttpPost]
        public string IndexListReport3Async(int PageIndex = 1, int PageSize = 1000000000, string Search = "", string BeginDate = "", string EndDate = "", int State = -1)
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@BillNameID",SqlDbType.Int),
                                            new SqlParameter("@PageIndex",SqlDbType.Int),
                                            new SqlParameter("@PageSize",SqlDbType.Int),
                                            new SqlParameter("@Search",SqlDbType.NVarChar,40000),
                                            new SqlParameter("@BeginDate",SqlDbType.DateTime),
                                            new SqlParameter("@EndDate",SqlDbType.DateTime),

                };
            parameters[0].Value = 8;
            parameters[1].Value = PageIndex;
            parameters[2].Value = PageSize;
            parameters[3].Value = Search;
            parameters[4].Value = BeginDate;
            parameters[5].Value = EndDate;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBillJiaoFuReport", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }


        public ActionResult ReportUser()
        {
            DataTable udt = FreeADO.DBUtility.DbHelperSQL.Query("select * from Users where DepartmentID=1 and IsDeleted=0").Tables[0];
            string s = "";
            foreach (DataRow item in udt.Rows)
            {
                s = s + @"<option value=""" + item["ID"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
            }
            ViewBag.Users = s;
            return View();
        }

        [HttpPost]
        public string ReportUserAsync(int PageIndex = 1, int PageSize = 1000000000, int UserID = 0, string BeginDate = "", string EndDate = "")
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@PageIndex",SqlDbType.Int),
                                            new SqlParameter("@PageSize",SqlDbType.Int),
                                            new SqlParameter("@BeginDate",SqlDbType.DateTime),
                                            new SqlParameter("@EndDate",SqlDbType.DateTime),
                                            new SqlParameter("@UserID",SqlDbType.Int),
            };
            parameters[0].Value = PageIndex;
            parameters[1].Value = PageSize;
            parameters[2].Value = BeginDate;
            parameters[3].Value = EndDate;
            parameters[4].Value = UserID;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBill8BillDetailsListForUser", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        public ActionResult IndexListReport4()
        {
            return View();
        }
        public ActionResult Print(int BillIndexID)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select BillIndex8.*,isnull(Goods.Code,'') as GoodsCode,isnull(Goods.Name,'') as GoodsName,isnull(Goods.Model,'') as GoodsModel
				,isnull(SGoods.Code,'') as SourceGoodsCode,isnull(SGoods.Name,'') as SourceGoodsName,isnull(SGoods.Model,'') as SourceGoodsModel
                ,TouUsers.Name as TouMan,LingUsers.Name as LengMan,dbo.Fn_Value_GetChengRate(8,BillIndex8.ID) as ChengRate
FROM BillIndex8 left join Goods on BillIndex8.GoodsID=Goods.ID left join Goods as SGoods on BillIndex8.SourceGoodsID=SGoods.ID
        left join Users as TouUsers on BillIndex8.TouUserID=TouUsers.ID
        left join Users as LingUsers on BillIndex8.LingUserID=LingUsers.ID
WHERE BillIndex8.ID=" + BillIndexID).Tables[0];
            string sssss = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
            Models.MVC.Bill8IndexModel m = new Models.MVC.Bill8IndexModel();
            if (dt.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.Bill8IndexModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];
                m.CreateDate = Convert.ToDateTime(m.CreateDate).ToString("yyyy-MM-dd");
                
            }
            ViewBag.model = m;
            SqlParameter[] parameters = {
                                            new SqlParameter("@BillNameID",SqlDbType.Int),
                                            new SqlParameter("@IndexID",SqlDbType.Int),

                };
            parameters[0].Value = 8;
            parameters[1].Value = BillIndexID;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBillDetailsListForPrint", parameters, "aaa", 3000);
            string s = "";
            foreach (DataRow item in ds.Tables[1].Rows)
            {
                s = s + @"
            <tr style=""height:20px;"">
                <td align=""center"">" + item["ProName"].ToString() + @"</td>
                <td align=""center"">" + item["InQty"].ToString() + @"</td>
                <td align=""center"">" + item["FailQty"].ToString() + @"</td>
                <td align=""center"">" + item["OutQty"].ToString() + @"</td>
                <td align=""center"">" + item["Operator"].ToString() + @"</td>
                <td align=""center"">" + item["CreateDate"].ToString() + @"</td>
                <td align=""center"">" + item["TaskTime"].ToString() + @"</td>
            </tr>
";
            }
            for (int i = 0; i < 22 - ds.Tables[1].Rows.Count; i++)
            {
                s = s + @"
            <tr style=""height:20px;"">
                <td align=""center""></td>
                <td align=""center""></td>
                <td align=""center""></td>
                <td align=""center""></td>
                <td align=""center""></td>
                <td align=""center""></td>
                <td align=""center""></td>
            </tr>
";
            }
            ViewBag.TableTr = s;
            return View();
        }

        public FileResult Down(int ID)
        {


            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
declare @BillIndexID int=" + ID + @"
select 
bp.id as ProID
,ROW_NUMBER() over(order by bp.showindex) as RowID
,case when bd.BeginDate<'2000-01-01' then '' else isnull(Convert(nvarchar(10),bd.BeginDate,120),'') end as BeginDate
,isnull(Convert(nvarchar(10),bd.EndDate,120),'') as EndDate
,isnull(bd.ShaiXuanModel,bp.Remark) as ShaiXuanModel
,isnull(bd.InQty,0) as InQty
,isnull(bd.OutQty,0) as OutQty
,isnull(bd.FailQty,0) as FailQty
,pro.Name as ProName
,isnull(ou.Name,'') as UserName
,isnull(bd.Remark,'') as Remark
from BillProcedures8 as bp  left join BillDetails8 as bd on bd.BillProcedureID=bp.ID
inner join [Procedures] as pro on bp.ProcedureID=pro.ID
left join Users as ou on bd.OperatorUserID=ou.ID
where bp.BillIndexID=@BillIndexID
").Tables[0];
            DataTable dt1 = FreeADO.DBUtility.DbHelperSQL.Query(@"
select BillIndex8.*,isnull(Goods.Code,'') as GoodsCode,isnull(Goods.Name,'') as GoodsName,isnull(Goods.Model,'') as GoodsModel
                ,isnull(BillIndex4.GoodsID,0) as SourceGoodsID
				,isnull(SGoods.Code,'') as SourceGoodsCode,isnull(SGoods.Name,'') as SourceGoodsName,isnull(SGoods.Model,'') as SourceGoodsModel
                ,isnull(BillIndex4.Code,'') as SourceCode
                ,isnull(BillIndex4.DengJi,'') as SourceDengJi
                ,isnull(BillIndex4.WaiXing,'') as SourceWaiXing
FROM BillIndex8 left join Goods on BillIndex8.GoodsID=Goods.ID
        left join BillIndex4 on BillIndex8.BillIndex4ID=BillIndex4.ID
        left join Goods as SGoods on BillIndex4.GoodsID=SGoods.ID
WHERE BillIndex8.ID=" + ID).Tables[0];
            string sssss = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
            Models.MVC.Bill8IndexModel m = new Models.MVC.Bill8IndexModel();
            if (dt1.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.Bill8IndexModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt1))[0];
            }
            MemoryStream ms = Common.ExcelTool.Bill8ToExcel(dt, m);
            return File(ms, "application/vnd.ms-excel", m.Code + ".xls");
        }

    }
}